package wsp.dailymarket.deals;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map.Entry;
import javax.annotation.Resource;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.ManagedProperty;
import javax.faces.bean.RequestScoped;
import javax.faces.event.ComponentSystemEvent;
import javax.faces.model.ArrayDataModel;
import javax.faces.model.DataModel;
import javax.sql.DataSource;

/**
 * Represents a listing of deals
 * @author Matt
 */
@ManagedBean(name="dealView")
@RequestScoped
public class DealViewBean {

	@Resource(name = "jdbc/dmderby")
	private DataSource ds;
	@ManagedProperty(value = "#{catView}")
	private CategoryViewBean catViewBean;
	private ArrayList<DealBean> renderedList;
	// Properties for parameterized deal listing
	private HashMap<String, String> restMap;
	private int param_view_listStart;
	private int param_view_listCount;
	private String param_view_sortMacro;
	private String param_data_search;
	private String param_data_company;
	private String param_data_companyName;
	// View Flags
	private boolean can_render_table = false;
	private int max_pages = 1;
	// Parameter Values
	private static final String[] PARAMVALUES_SORTMACRO = {"newest", "ending", "price_asc", "price_desc"};
	private static final String[] PARAMDATA_SORTMACROS_FILTERS = {
		// SELECT ... WHERE
		"(STARTTIME < CURRENT_TIMESTAMP AND (TIMED = 0 OR ENDTIME > CURRENT_TIMESTAMP)) ", // newest
		"(STARTTIME < CURRENT_TIMESTAMP AND TIMED = 1 AND ENDTIME > CURRENT_TIMESTAMP) ", // ending (soonest)
		"(STARTTIME < CURRENT_TIMESTAMP AND (TIMED = 0 OR ENDTIME > CURRENT_TIMESTAMP)) ", // price asc
		"(STARTTIME < CURRENT_TIMESTAMP AND (TIMED = 0 OR ENDTIME > CURRENT_TIMESTAMP)) " // price desc
	};
	private static final String[] PARAMDATA_SORTMACROS_ORDERS = {
		"ORDER BY STARTTIME DESC", // newest
		"ORDER BY ENDTIME", // ending (soonest)
		"ORDER BY UNITPRICE", // price asc
		"ORDER BY UNITPRICE DESC" // price desc
	};
	private static final String PARAMDEFAULT_SORTMACRO = "newest";
	private static final int PARAMDEFAULT_LISTSTART = 0;
	private static final int PARAMDEFAULT_LISTCOUNT = 10;

	public DealViewBean() {
		this.param_view_listStart = PARAMDEFAULT_LISTSTART;
		this.param_view_listCount = PARAMDEFAULT_LISTCOUNT;
		this.param_view_sortMacro = PARAMDEFAULT_SORTMACRO;
		this.param_data_search = "";
		this.param_data_company = "";
		this.param_data_companyName = "";
		this.restMap = new HashMap<String, String>();
	}

	public void buildParameterizedDealList() throws SQLException {

		// Start with base query
		String sql = "SELECT APP.DEALS.*,APP.CLIENTS.COMPANYNAME,APP.CATEGORIES.CATEGORY_NAME FROM APP.DEALS INNER JOIN APP.CLIENTS ON APP.DEALS.COMPANYID=APP.CLIENTS.CLIENT_ID LEFT OUTER JOIN APP.CATEGORIES ON APP.DEALS.CATEGORY_ID = APP.CATEGORIES.ID";

		// Does this query already have a WHERE clause started?
		boolean where_clause_begun = false;
		String where = "";

		// Filter by category (if category is specified) (0 is also the "non-category" that bypasses the category filter)
		int cat_id = this.catViewBean.getViewCategoryID();
		if (cat_id > 0) {
			where += " WHERE CATEGORY_ID = " + cat_id;
			where_clause_begun = true;
		}

		// Filter by company ID (if specified)
		if (!"".equals(this.param_data_company)) {
			if (!where_clause_begun) {
				where += " WHERE ";
				where_clause_begun = true;
			} else {
				where += " AND ";
			}
			where += "COMPANYID = '" + this.param_data_company + "'";
		}

		// TODO Filter by a search string (if specified)

		// Apply Sort Macro - Filter
		if (!where_clause_begun) {
			where += " WHERE ";
			where_clause_begun = true;
		} else {
			where += " AND ";
		}
		int macro_id = Arrays.asList(PARAMVALUES_SORTMACRO).indexOf(this.param_view_sortMacro);
		if (macro_id > 0 && macro_id < PARAMVALUES_SORTMACRO.length) {
			where += PARAMDATA_SORTMACROS_FILTERS[macro_id];
		} else {
			where += PARAMDATA_SORTMACROS_FILTERS[0];
		}

		// Run the query to find out the number of pages we can have
		int num_records = this.getNumRecords(where);
		this.max_pages = ((num_records - 1) / this.param_view_listCount) + 1;

		// Apply Sort Macro - Sort
		if (macro_id > 0 && macro_id < PARAMVALUES_SORTMACRO.length) {
			where += PARAMDATA_SORTMACROS_ORDERS[macro_id];
		} else {
			where += PARAMDATA_SORTMACROS_ORDERS[0];
		}

		// Merge sql statements
		sql += where;

		// Check to make sure we aren't going off the end of the result set
		if (this.param_view_listStart >= num_records) {
			this.param_view_listStart = (this.max_pages - 1) * this.param_view_listCount;
		}

		// Apply Row Select based on pagination
		if (this.param_view_listStart > 0) {
			sql += " OFFSET " + this.param_view_listStart + " ROWS";
		}
		sql += " FETCH FIRST " + this.param_view_listCount + " ROWS ONLY";

		// Run the query
		this.renderedList = this.buildList(sql);
	}

	public ArrayList<DealBean> getHomePageLatestDeals() throws SQLException {
		String sql = "SELECT APP.DEALS.*,APP.CLIENTS.COMPANYNAME,APP.CATEGORIES.CATEGORY_NAME FROM APP.DEALS INNER JOIN APP.CLIENTS ON APP.DEALS.COMPANYID=APP.CLIENTS.CLIENT_ID LEFT OUTER JOIN APP.CATEGORIES ON APP.DEALS.CATEGORY_ID = APP.CATEGORIES.ID WHERE " + PARAMDATA_SORTMACROS_FILTERS[0] + PARAMDATA_SORTMACROS_ORDERS[0] + " FETCH FIRST 3 ROWS ONLY";
		return this.buildList(sql);
	}

	public ArrayList<DealBean> getHomePageEndingSoonDeals() throws SQLException {
		String sql = "SELECT APP.DEALS.*,APP.CLIENTS.COMPANYNAME,APP.CATEGORIES.CATEGORY_NAME FROM APP.DEALS INNER JOIN APP.CLIENTS ON APP.DEALS.COMPANYID=APP.CLIENTS.CLIENT_ID LEFT OUTER JOIN APP.CATEGORIES ON APP.DEALS.CATEGORY_ID = APP.CATEGORIES.ID WHERE " + PARAMDATA_SORTMACROS_FILTERS[1] + PARAMDATA_SORTMACROS_ORDERS[1] + " FETCH FIRST 3 ROWS ONLY";
		return this.buildList(sql);
	}

	private ArrayList<DealBean> buildList(String sql) throws SQLException {
		ArrayList<DealBean> dealList = new ArrayList<DealBean>();
		Connection conn = null;
		try {
			conn = ds.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				DealBean newDeal = new DealBean();
				newDeal.populate(
						rs.getInt("ID"),
						rs.getString("COMPANYID"),
						rs.getString("COMPANYNAME"),
						rs.getString("SHORTDESC"),
						rs.getString("LONGDESC"),
						rs.getFloat("UNITPRICE"),
						rs.getInt("MINIMUMQTY"),
						rs.getInt("MAXIMUMQTY"),
						rs.getInt("MINUSERQTY"),
						rs.getInt("MAXUSERQTY"),
						rs.getInt("TIMED") == 1,
						rs.getTimestamp("STARTTIME"),
						rs.getTimestamp("ENDTIME"),
						rs.getInt("DEFAULT_IMAGE"),
						rs.getInt("CATEGORY_ID"),
						rs.getString("CATEGORY_NAME"));
				dealList.add(newDeal);
			}
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
		this.can_render_table = dealList.size() > 0;
		return dealList;
	}

	private int getNumRecords(String sql) throws SQLException {
		Connection conn = null;
		try {
			conn = ds.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT COUNT(*) AS NUM_DEALS FROM APP.DEALS INNER JOIN APP.CLIENTS ON APP.DEALS.COMPANYID=APP.CLIENTS.CLIENT_ID LEFT OUTER JOIN APP.CATEGORIES ON APP.DEALS.CATEGORY_ID = APP.CATEGORIES.ID " + sql);
			if (rs.next()) {
				return rs.getInt("NUM_DEALS");
			}
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
		return 0;
	}

	public void setCatViewBean(CategoryViewBean newValue) {
		this.catViewBean = newValue;
	}

	public CategoryViewBean getCatViewBean() {
		return this.catViewBean;
	}

	public void setPage(String newValue) {
		try {
			int page = Integer.parseInt(newValue);
			this.param_view_listStart = (page - 1) * this.param_view_listCount;
		} catch (NumberFormatException ex) {
			this.param_view_listStart = 0;
		}
		this.restMap.put("page", this.getPage());
	}

	public String getPage() {
		return String.valueOf((this.param_view_listStart / this.param_view_listCount) + 1);
	}

	public void setCount(String newValue) {
		try {
			this.param_view_listCount = Integer.parseInt(newValue);
		} catch (NumberFormatException ex) {
			this.param_view_listCount = PARAMDEFAULT_LISTCOUNT;
		}
		this.restMap.put("count", this.getCount());
	}

	public String getCount() {
		return String.valueOf(this.param_view_listCount);
	}

	public void setSortMacro(String newValue) {
		// Set macro
		if (Arrays.asList(PARAMVALUES_SORTMACRO).contains(newValue)) {
			if (!this.param_view_sortMacro.equals(newValue)) {
				// Reset page to Page 1
				this.setPage("1");
			}
			this.param_view_sortMacro = newValue;
		} else {
			this.param_view_sortMacro = PARAMDEFAULT_SORTMACRO;
		}
		this.restMap.put("sort", this.param_view_sortMacro);
	}

	public String getSortMacro() {
		return this.param_view_sortMacro;
	}

	public void setFilterCompany(String newValue) {
		try {
			if (!newValue.equals(this.param_data_company)) {
				// Reset page
				this.setPage("1");
			}
			this.param_data_company = newValue;

			Connection conn = null;
			try {
				conn = ds.getConnection();
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery("SELECT companyname FROM APP.CLIENTS WHERE client_id = '" + this.param_data_company + "'");
				if (rs.next()) {
					this.param_data_companyName = rs.getString("COMPANYNAME");
				} else {
					this.param_data_company = "";
				}
			} finally {
				if (conn != null) {
					conn.close();
				}
			}
		} catch (SQLException ex) {
			ex.printStackTrace(System.err);
			this.param_data_company = "";
		}
		this.restMap.put("partner", this.getFilterCompany());
	}

	public String getFilterCompany() {
		return this.param_data_company;
	}

	public void setSearchQuery(String newValue) {
		if (!this.param_data_search.equals(newValue)) {
			// Reset page to Page 1
			this.setPage("1");
		}
		this.param_data_search = newValue;
		this.restMap.put("q", newValue);
	}

	public String getSearchQuery() {
		return this.param_data_search;
	}

	public void setCategoryID(String newValue) {
		try {
			int cat_id = Integer.parseInt(newValue);
			this.catViewBean.setViewCategoryID(cat_id);
		} catch (NumberFormatException ex) {
			this.catViewBean.setViewCategoryID(0);
		}
		this.restMap.put("cat", this.getCategoryID());
	}

	public String getCategoryID() {
		return String.valueOf(this.catViewBean.getViewCategoryID());
	}

	public String buildRestLink(String modifiedField, String modifiedValue) {
		String rest = "deals.do?";
		HashMap<String, String> modified = new HashMap<String, String>();
		modified.putAll(this.restMap);
		modified.put(modifiedField, modifiedValue);
		Iterator<Entry<String, String>> iter = modified.entrySet().iterator();
		while (iter.hasNext()) {
			Entry<String, String> item = iter.next();
			rest += item.getKey() + "=" + item.getValue();
			if (iter.hasNext()) {
				rest += "&";
			}
		}
		return rest;
	}

	public String buildRestLink(String excludedField) {
		String rest = "deals.do?";
		HashMap<String, String> modified = new HashMap<String, String>();
		modified.putAll(this.restMap);
		modified.put(excludedField, "");
		Iterator<Entry<String, String>> iter = modified.entrySet().iterator();
		while (iter.hasNext()) {
			Entry<String, String> item = iter.next();
			rest += item.getKey() + "=" + item.getValue();
			if (iter.hasNext()) {
				rest += "&";
			}
		}
		return rest;
	}

	public String buildRestFirstPageLink() {
		return this.buildRestLink("page", "1");
	}

	public String buildRestNextPageLink() {
		int page = (this.param_view_listStart / this.param_view_listCount) + 2;
		if (page > this.max_pages) {
			page = max_pages;
		}
		return this.buildRestLink("page", String.valueOf(page));
	}

	public String buildRestPrevPageLink() {
		int page = (this.param_view_listStart / this.param_view_listCount);
		if (page < 1) {
			page = 1;
		}
		return this.buildRestLink("page", String.valueOf(page));
	}

	public String buildRestLastPageLink() {
		return this.buildRestLink("page", String.valueOf(max_pages));
	}

	public String buildSortTabStyle(String sort) {
		if (this.param_view_sortMacro.equals(sort)) {
			return "tab selected";
		} else {
			return "tab";
		}
	}

	public boolean getRenderable() {
		return this.can_render_table;
	}

	public String getHumanReadableNoneDescription() {
		String human = "There are no deals ";
		if (this.catViewBean.getViewCategoryID() > 0) {
			human += "in the category \"" + this.catViewBean.getViewCategory().getName() + "\" ";
		}
		if (!"".equals(this.param_data_company)) {
			human += "offered by \"" + this.param_data_companyName + "\" ";
		}
		if (this.param_data_search.length() > 0) {
			human += "that matched your search.";
		} else {
			human += "at this time.";
		}
		return human;
	}

	public String getNoneAlertStyle() {
		if (this.catViewBean.getViewCategoryID() > 0) {
			return "notification error";
		}
		if (!"".equals(this.param_data_company)) {
			return "notification error";
		}
		if (this.param_data_search.length() > 0) {
			return "notification error";
		}
		return "notification";

	}

	public DataModel<DealBean> getRenderedModel() throws SQLException {
		if (this.renderedList == null) {
			this.buildParameterizedDealList();
		}
		return new ArrayDataModel(this.renderedList.toArray());
	}

	public void preRenderView(ComponentSystemEvent event) throws SQLException {
		this.buildParameterizedDealList();
	}

	public int getCurrentPage() {
		return (this.param_view_listStart / this.param_view_listCount) + 1;
	}

	public int getMaxPages() {
		return this.max_pages;
	}
}
